package com.carlinfo.myjdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class PrepareMain
{

	public static void main(String[] args)
	{
		/**
		 * 查询所有数据
		 */
//		seclectAll();
		String name = "张三";
		String sex = "' or 1=1 or id = '";
		String sex1 = "男";
		/**
		 * 查询一张数据，sql注入
		 */
//		selectOne(name,sex);
		/**
		 * 查询一条数据，用预处理
		 */
//		selectOne1(name,sex1);
		List<Object> list = new ArrayList<Object>();
		list.add("李菲菲");
		list.add("女");
		list.add(18);
		list.add(new Date());
		list.add(66.6);
		/**
		 * 插入一条数据
		 */
//		insertOne(list);
		String na = "李小飞";
		/**
		 * 删除一条
		 */
		deleteOne(na);
	}

	private static void deleteOne(String na)
	{
		String url = "jdbc:mysql:///student?useSSL=false&user=root&password=123456";
		String sql = "delete from mydata where name = ? ;";
		Connection conn = null;
		PreparedStatement pst = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
			pst = conn.prepareStatement(sql);
			pst.setObject(1, na);
			int res = pst.executeUpdate();
			System.out.println("影响条数:"+res);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally {
			try
			{
				if(pst!=null) {
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn!=null) {
					conn.close();
					conn = null;
				}	
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
	}

	private static void insertOne(List<Object> list)
	{

		String url = "jdbc:mysql:///student?useSSL=false&user=root&password=123456";
		String sql ="INSERT INTO `mydata`(`name`, `sex`, `age`, `publicTime`, `math`) VALUES (?, ?, ?, ?, ?)";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
			pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pst.setObject(1, list.get(0));
			pst.setObject(2, list.get(1));
			pst.setObject(3, list.get(2));
			pst.setObject(4, list.get(3));
			pst.setObject(5, list.get(4));
			int res = pst.executeUpdate();
			rs= pst.getGeneratedKeys();
			if(rs.next()) {
				System.out.println("id是："+rs.getInt(1));
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally {
			try
			{
				if(rs!=null) {
					rs.close();
					rs = null;
				}	
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pst!=null) {
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn!=null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
	
	
	}

	private static void selectOne1(String name, String sex)
	{
		String url = "jdbc:mysql:///student?useSSL=false&user=root&password=123456";
		String sql ="select * from mydata where name = ? and sex = ?";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
			pst = conn.prepareStatement(sql);
			pst.setObject(1, name);
			pst.setObject(2, sex);
			rs = pst.executeQuery();
			if(rs.next()) {
				int id =  rs.getInt("id");
				double math = rs.getDouble("math");
				System.out.println("id:"+id+",math:"+math);
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally {

			try
			{
				if(rs!=null) {
					rs.close();
					rs = null;
				}	
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pst!=null) {
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn!=null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		
		}
		
	
	}

	/**
	 * sql注入的问题
	 * @param name
	 * @param sex
	 */
	private static void selectOne(String name,String sex)
	{
		String url = "jdbc:mysql:///student?useSSL=false&user=root&password=123456";
		String sql ="select * from mydata where name = '"+name+"'and sex = '"+sex+"'";
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if(rs.next()) {
				int id =  rs.getInt("id");
				double math = rs.getDouble("math");
				System.out.println("id:"+id+",math:"+math);
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally{

			try
			{
				if(rs!=null) {
					rs.close();
					rs = null;
				}	
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(st!=null) {
					st.close();
					st = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn!=null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		
		}
		
	}

	private static void seclectAll()
	{
		String url = "jdbc:mysql:///student?useSSL=false&user=root&password=123456";
		String sql ="select * from mydata";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url);
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()) {
				String name = rs.getString("name");
				Date publicTime = rs.getTimestamp("publicTime");
				System.out.println(name+"\t"+publicTime);
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally {

			try
			{
				if(rs!=null) {
					rs.close();
					rs = null;
				}	
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pst!=null) {
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn!=null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		
		}
	}

}
